package com.howard.poi;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.ResourceUtils;

/**
 *
 * @author Howard.He
 *
 */
public class ExcelHandler {
	private static Logger logger = LoggerFactory.getLogger(ExcelHandler.class);

	public static Sheet getExcelForSheet(String resource)
			throws FileNotFoundException, IOException {
		if (StringUtils.isBlank(resource)) {
			throw new IllegalArgumentException("resource must not be null");
		}
		File file = ResourceUtils.getFile(resource);
		return getExcelForSheet(file);
	}

	public static Sheet getExcelForSheet(File file)
			throws FileNotFoundException, IOException {
		if (file == null) {
			throw new IllegalArgumentException("file must not be null");
		}
		String fileName = file.getName();
		String extName = StringUtils.substring(fileName,
				StringUtils.indexOf(fileName, ".") + 1);
		logger.info("extName: " + extName);
		POIFSFileSystem excel = new POIFSFileSystem(new FileInputStream(file));
		Workbook wb = new HSSFWorkbook(excel);
		Sheet sheet = wb.getSheetAt(0);
		return sheet;
	}

	/**
	 * 读excel
	 *
	 * @param Sheet
	 *            sheet
	 * @return List<Map<String, Object>>
	 * @throws FileNotFoundException
	 */
	public static List<Map<String, Object>> readExcel(Sheet sheet)
			throws FileNotFoundException, IOException {
		List<Map<String, Object>> datas = new ArrayList<Map<String, Object>>();
		Row firstRow = sheet.getRow(sheet.getFirstRowNum());
		for (Row row : sheet) {
			if (row.getRowNum() != sheet.getFirstRowNum()) {
				Map<String, Object> rowData = new HashMap<String, Object>();
				for (Cell cell : row) {
					CellReference reference = new CellReference(
							row.getRowNum(), cell.getColumnIndex());
					logger.info("row: " + row.getRowNum() + ", cell: "
							+ cell.getColumnIndex() + "value:[ "
							+ reference.formatAsString() + "]");
					rowData.put(firstRow.getCell(cell.getColumnIndex())
							.getStringCellValue(), getCellValue(cell));
				}
				datas.add(rowData);
			}
		}
		return datas;
	}

	/**
	 * 读excel
	 *
	 * @param resource
	 *            资源文件名称
	 * @return List<Map<String, Object>>
	 * @throws FileNotFoundException
	 */
	public static List<Map<String, Object>> readExcel(String resource)
			throws FileNotFoundException, IOException {
		File file = ResourceUtils.getFile(resource);
		return readExcel(file);
	}

	/**
	 *
	 * @param file
	 * @return
	 */
	public static List<Map<String, Object>> readExcel(File file)
			throws FileNotFoundException, IOException {
		if (file == null) {
			throw new IllegalArgumentException("file must not be null");
		}
		Sheet sheet = getExcelForSheet(file);
		return readExcel(sheet);
	}

	public static Object getCellValue(Cell cell) {
		int type = cell.getCellType();
		switch (type) {
		case Cell.CELL_TYPE_STRING:
			return cell.getRichStringCellValue().getString();
		case Cell.CELL_TYPE_NUMERIC:
			if (DateUtil.isCellDateFormatted(cell)) {
				return cell.getDateCellValue();
			} else {
				return cell.getNumericCellValue();
			}
		case Cell.CELL_TYPE_BOOLEAN:
			return cell.getBooleanCellValue();
		case Cell.CELL_TYPE_FORMULA:
			return cell.getCellFormula();
		default:
			logger.debug("unkown cell type!!!");
			return null;

		}
	}

	/**
	 * 先读取excel 文件
	 *
	 * @param resource
	 * @throws FileNotFoundException
	 * @throws IOException
	 */
	public static void writeExcel(String resource)
			throws FileNotFoundException, IOException {
//		Sheet sheet = getExcelForSheet(resource);
//		List<Map<String, Object>> datas = readExcel(sheet);
	}

	/**
	 * 写文件
	 *
	 * @param datas
	 */
	public static void writeExcel(List<Map<String, Object>> datas, Sheet sheet) {
		if (sheet == null) {
			Workbook wb = new HSSFWorkbook();
			sheet = wb.createSheet();
		}
		boolean flag = true;
		Row row = null;
		Cell cell = null;
		int rownum = 1;
		for (Map<String, Object> map : datas) {
			if (flag) {
				row = sheet.createRow(0);
				int cellnum = 0;
				for (String header : map.keySet()) {
					cell = row.createCell(cellnum++);
					cell.setCellValue(header);
				}
				flag = false;
			} else {
				row = sheet.createRow(rownum++);
				int cellnum = 0;
				for (Entry<String, Object> entry : map.entrySet()) {
					row.createCell(cellnum++);
					cell.setCellValue((Double) entry.getValue());
					//TODO
				}
			}
		}
	}
}
